package com.jinbai.dao;

import com.jinbai.model.Article;
import com.jinbai.util.DBUtil;
import lombok.Data;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class ArticleDao {
    @SneakyThrows
    public int selectArticleCountByUid(int uid) {
        try (Connection con = DBUtil.connection()) {
            String sql = "select count(*) from blog.articles where uid = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public int selectTypeCountByUid(int uid) {
        try (Connection con = DBUtil.connection()) {
            String sql = "select count(distinct type) from blog.articles where uid = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public Article selectOneArticleByAid(int aid) {
        Article article = new Article();
        try (Connection con = DBUtil.connection()) {
            String sql = "select aid, publishedAt, title, content from blog.articles where aid = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, aid);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    article.aid = rs.getInt("aid");
                    article.publishedAt = rs.getDate("publishedAt");
                    article.content = rs.getString("content");
                    article.title = rs.getString("title");
                    return article;
                }
                return null;
            }
        }
    }

    @SneakyThrows
    public List<Article> selectArticleListByUid(int uid) {
        List<Article> ans = new LinkedList<>();
        try (Connection con = DBUtil.connection()) {
            String sql = "select aid, uid, title, type, publishedAt, content from blog.articles where uid = ? order by publishedAt desc";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setInt(1, uid);
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        Article article = new Article();
                        article.aid = rs.getInt("aid");
                        article.uid = rs.getInt("uid");
                        article.title = rs.getString("title");
                        article.type = rs.getString("type");
                        article.publishedAt = rs.getDate("publishedAt");
                        article.content = rs.getString("content");
                        ans.add(article);
                    }
                }
            }
        }
        return ans;
    }

    @SneakyThrows
    public int insert(Integer uid, String title, String type, Date date, String content) {
        try (Connection con = DBUtil.connection()) {
            String sql = "insert into blog.articles (uid, title, type, publishedAt, content) values (?, ?, ?, ?, ?)";
            try (PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                ps.setInt(1, uid);
                ps.setString(2, title);
                ps.setString(3, type);
//                ps.setDate(4, (java.sql.Date) date);
                Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String publishedAt = format.format(date);
                ps.setString(4, publishedAt);
                ps.setString(5, content);
                ps.executeUpdate();

                try (final ResultSet rs = ps.getGeneratedKeys()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }
}
